<?xml version="1.0" ?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:lzc="http://schemas.icitadel.com/Lazarus/Compiled.xsd" version="1.0">

	<xsl:output method="text"/>

	<xsl:include href="../../common.xsl" />
	<xsl:include href="../../convert.xsl" />

   <xsl:template match="lzc:project">
-- Project: <xsl:value-of select="@name" />
-- Table and Relationship creation scripts.
      
      <!-- Create the tables -->
<xsl:text>-- Table Creation

SET ANSI_NULLS ON 
GO

</xsl:text>

<xsl:for-each select="lzc:tables/lzc:table[ count( lzc:fields/lzc:field ) &gt; 0 ]">
	<xsl:variable name="TABLE_NAME">
		<xsl:text>tbl</xsl:text>
		<xsl:value-of select="@name" />
	</xsl:variable>
<xsl:text>
/*
Table Creation -- </xsl:text><xsl:value-of select="@name" />
<xsl:if test="lzc:description"><xsl:text>

</xsl:text>
<xsl:value-of select="lzc:description" />
</xsl:if><xsl:text>
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = '</xsl:text>
<xsl:value-of select="$TABLE_NAME" />
<xsl:text>' ) )
CREATE TABLE </xsl:text><xsl:value-of select="ancestor::lzc:project/@owner" />.[<xsl:value-of select="$TABLE_NAME" />] 
(
   <xsl:for-each select="lzc:fields/lzc:field">
      <xsl:text>[</xsl:text><xsl:value-of select="@dbname" /><xsl:text>]</xsl:text>
      <xsl:value-of select="$space" />
      <xsl:call-template name="getSqlTableType">
         <xsl:with-param name="pFieldType" select="@type" />
      </xsl:call-template>
      <xsl:if test="( string-length( @size ) &gt; 0 ) and ( @size &gt; 0 )">
			<xsl:text>( </xsl:text>
			<xsl:value-of select="@size" />
			<xsl:if test="( string-length( @scale ) &gt; 0 ) and ( @scale &gt; 0 )">
				<xsl:text>, </xsl:text>
				<xsl:value-of select="@scale" />
			</xsl:if>
			<xsl:text> )</xsl:text>
		</xsl:if>
      <xsl:if test="@required = 'True'"> NOT NULL</xsl:if>
	   <xsl:if test="position() &lt; count( ../lzc:field )">
		   <xsl:text>,</xsl:text>
	   </xsl:if>
	   <xsl:if test="lzc:description">
		   <xsl:text> /* </xsl:text>
		   <xsl:value-of select="lzc:description" />
		   <xsl:text> */</xsl:text>
	   </xsl:if>
	   <xsl:text>
   </xsl:text>
   </xsl:for-each>
)<xsl:if test="@filegroup">
	<xsl:text> ON </xsl:text><xsl:value-of select="@filegroup" />
</xsl:if>
<xsl:text>
</xsl:text>      
      </xsl:for-each>
      
GO

	<!-- Create the PK and Clustered Indexes -->
	<xsl:text>-- PK Index Creation
</xsl:text>
<xsl:for-each select="lzc:tables/lzc:table[ count( lzc:fields/lzc:field ) &gt; 0 ]">
		<xsl:variable name="TABLE_NAME">
			<xsl:text>tbl</xsl:text>
			<xsl:value-of select="@name" />
		</xsl:variable>
		<xsl:variable name="v_ClusteredIndexField"><xsl:value-of select="lzc:fields/lzc:field[ lzc:indexes/lzc:index/@type = $INDEXTYPE_CLUSTERED ]/@name" /></xsl:variable>
		<xsl:variable name="v_PKField"><xsl:value-of select="lzc:fields/lzc:field[ lzc:indexes/lzc:index/@type = $INDEXTYPE_PRIMARY ]/@name" /></xsl:variable>
		<xsl:variable name="v_PKIndexName"><xsl:value-of select="lzc:fields/lzc:field/lzc:indexes/lzc:index[ @type = $INDEXTYPE_PRIMARY ]/@name" /></xsl:variable>
		<xsl:variable name="v_PKFieldFilegroup"><xsl:value-of select="lzc:fields/lzc:field[ lzc:indexes/lzc:index/@type = $INDEXTYPE_PRIMARY ]/@filegroup" /></xsl:variable>

<xsl:text>IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = '</xsl:text>
<xsl:value-of select="$TABLE_NAME" />
<xsl:text>' ) AND ( syso2.name = '</xsl:text>
<xsl:value-of select="$v_PKIndexName" />
<xsl:text>' ) ) )
</xsl:text>
		<xsl:text>   ALTER TABLE [</xsl:text>
		<xsl:value-of select="$TABLE_NAME" />
		<xsl:text>] ADD CONSTRAINT [</xsl:text><xsl:value-of select="$v_PKIndexName" /><xsl:text>] PRIMARY KEY</xsl:text>
		<xsl:if test="$v_ClusteredIndexField = $v_PKField">
			<xsl:text> CLUSTERED</xsl:text>
		</xsl:if>
		<xsl:text> ( [</xsl:text>
			<xsl:value-of select="$v_PKField" />
		<xsl:text>] )</xsl:text>
		<xsl:if test="not( $v_PKFieldFilegroup = '' )">
			<xsl:text> ON </xsl:text><xsl:value-of select="$v_PKFieldFilegroup" />
		</xsl:if>
		<xsl:text>
GO

</xsl:text>
</xsl:for-each>      

      <!-- Create the defaults -->
<xsl:text>-- Default Creation</xsl:text>
      <xsl:for-each select="lzc:tables/lzc:table[ count( lzc:fields/lzc:field ) &gt; 0 ]">
         <xsl:if test="count( lzc:fields/lzc:field[ @default ] ) &gt; 0">
   <xsl:for-each select="lzc:fields/lzc:field[ @default ]">
		<xsl:variable name="DEFAULT_NAME">
			<xsl:text>DF_</xsl:text>
			<xsl:value-of select="ancestor::lzc:table/@name" />
			<xsl:text>_</xsl:text>
			<xsl:value-of select="@name" />
		</xsl:variable>
<xsl:text>
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.name = '</xsl:text>
<xsl:value-of select="$DEFAULT_NAME" />
<xsl:text>' AND xtype='D' ) )
	ALTER TABLE </xsl:text><xsl:value-of select="ancestor::lzc:project/@owner" />.[tbl<xsl:value-of select="ancestor::lzc:table/@name" /><xsl:text>] ADD CONSTRAINT [DF_</xsl:text><xsl:value-of select="ancestor::lzc:table/@name" />_<xsl:value-of select="@name" /><xsl:text>] DEFAULT ( </xsl:text><xsl:value-of select="@default" /> ) FOR [<xsl:value-of select="@dbname" /><xsl:text>];
   </xsl:text>
   </xsl:for-each>
   </xsl:if>
   </xsl:for-each>
   
SET ANSI_NULLS OFF
GO   
   </xsl:template>

</xsl:stylesheet>